package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import dao.BaseDao;
import dao.PetDao;
import entity.Pet;

public class PetDaoImpl extends BaseDao implements PetDao {

	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	
	@Override
	public List<Pet> listAll() {
		List<Pet> list = new ArrayList<Pet>();
		try {
			conn = super.getConnection();
			String sql;
			sql = "SELECT * FROM pet";
			pstmt = conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while (rs.next()) {
				Pet pet = new Pet();
				pet.setPetId(rs.getInt("petid"));
				pet.setPetName(rs.getString("petname"));
				pet.setPetBreed(rs.getInt("petBreed"));
				pet.setPetSex(rs.getInt("petSex"));
				pet.setBirthday(rs.getString("birthday"));
				pet.setDescription(rs.getString("description"));
				list.add(pet);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			super.closeAll(conn, pstmt, rs);
		}
		return list;
	}

	@Override
	public List<Pet> ByBreed(int petBreed) {
		List<Pet> list = new ArrayList<Pet>();
		try {
			conn = super.getConnection();
			String sql = "SELECT * FROM pet WHERE petBreed = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, petBreed);
			rs=pstmt.executeQuery();
			while (rs.next()) {
				Pet pet = new Pet();
				pet.setPetId(rs.getInt("petid"));
				pet.setPetName(rs.getString("petname"));
				pet.setPetBreed(rs.getInt("petBreed"));
				pet.setPetSex(rs.getInt("petSex"));
				pet.setBirthday(rs.getString("birthday"));
				pet.setDescription(rs.getString("description"));
				list.add(pet);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			super.closeAll(conn, pstmt, rs);
		}
		return list;
	}

	@Override
	public int add(Pet pet) {
		int result = 0;
		try {
			conn = super.getConnection();
			String sql = "INSERT INTO pet(petName,petBreed,petSex,birthday,description) VALUES(?,?,?,?,?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, pet.getPetName());
			pstmt.setInt(2, pet.getPetBreed());
			pstmt.setInt(3, pet.getPetSex());
			pstmt.setString(4, pet.getBirthday());
			pstmt.setString(5, pet.getDescription());
			result = pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			super.closeAll(conn, pstmt, rs);
		}
		return result;
	}

	@Override
	public int ByName(String petName) {
		int result = 0;
		try {
			conn = super.getConnection();
			String sql = "SELECT COUNT(*) AS count FROM pet WHERE petName=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, petName);
			rs=pstmt.executeQuery();
			if (rs.next()) {
				result = rs.getInt("count");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			super.closeAll(conn, pstmt, rs);
		}
		return result;
	}

}
